﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace ManagementProject
{
    public partial class frmSupplier : Form
    {
        public frmSupplier()
        {
            InitializeComponent();
            LoadSupplier();
            txtSupID.Enabled = false;
            txtSearch.Enabled = true;
        }
        
        private void ClearAll()
        {
            txtAddress.Clear();
            txtCity.Clear();
            txtCompanyName.Clear();
            txtContactName.Clear();
            txtContacttitle.Clear();
            txtFax.Clear();
            txtPhone.Clear();
            txtPostalCode.Clear();
            txtRegion.Clear();
            txtSearch.Clear();
            txtSupID.Clear();
            errorProvider1.Clear();
            cbbCountry.SelectedIndex = -1;
        }
        private void btNew_Click(object sender, EventArgs e)
        {   
            ClearAll();
            btnAdd.Enabled = true;
        }
        private void AddSuppliers()
        {
            try
            {
                SqlConnection con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "InsertSuppliers";

                var error = validateInput();
                if (error)
                {
                    SqlParameter param = new SqlParameter("@companyname", txtCompanyName.Text);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@contactname", txtContactName.Text);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@contacttitle", txtContacttitle.Text);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@address", txtAddress.Text);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@city", txtCity.Text);
                    cmd.Parameters.Add(param);
                    if (txtRegion.Text != "")
                    {
                        param = new SqlParameter("@region", txtRegion.Text);
                    }
                    else param = new SqlParameter("@region", DBNull.Value);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@postalcode", txtPostalCode.Text);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@country", cbbCountry.Text);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@phone", txtPhone.Text);
                    cmd.Parameters.Add(param);
                    if (txtFax.Text != "")
                    {
                        param = new SqlParameter("@fax", txtFax.Text);
                    }
                    else param = new SqlParameter("@fax", DBNull.Value);
                    cmd.Parameters.Add(param);

                    gvSupplier.Rows.Clear();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    MessageBox.Show("Successfully");
                }
                else
                {
                    MessageBox.Show("Cannot be blank <3");
                    ClearAll();
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Add fail");
            }
            
        }

       
        private void gvSupplier_SelectionChanged(object sender, EventArgs e)
        {
            try
            {
                    
                if (gvSupplier.SelectedRows.Count > 0)
                {
                    btnAdd.Enabled = false;
                    ClearAll();

                    txtSupID.Enabled = false;
                    DataGridViewRow r = gvSupplier.SelectedRows[0];
                    txtSupID.Text = r.Cells["clSupID"].Value.ToString();
                    txtCompanyName.Text = r.Cells["clCompanyName"].Value.ToString();
                    txtContactName.Text = r.Cells["clContactName"].Value.ToString();
                    txtAddress.Text = r.Cells["clAddress"].Value.ToString();
                    txtCity.Text = r.Cells["clCity"].Value.ToString();
                    txtContacttitle.Text = r.Cells["clContacttitle"].Value.ToString();
                    txtFax.Text = r.Cells["clFax"].Value.ToString();
                    txtPhone.Text = r.Cells["clPhone"].Value.ToString();
                    cbbCountry.Text = r.Cells["clCountry"].Value.ToString();
                    txtPostalCode.Text = r.Cells["clPostalCode"].Value.ToString();
                    txtRegion.Text = r.Cells["clRegion"].Value.ToString();
                    txtAddress.Text = r.Cells["clAddress"].Value.ToString();
                }
            }
            catch (System.Exception ex)
            {
            
            }
            
        }
        private void UpdateSupplier()
        {
            try
            {
                SqlConnection con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "Updatesupplier";
                txtSupID.Enabled = false;

                SqlParameter param = new SqlParameter("@supplierid", txtSupID.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@contactname", txtContactName.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@companyname", txtCompanyName.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@contacttitle", txtContacttitle.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@address", txtAddress.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@city", txtCity.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@region", txtRegion.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@postalcode", txtPostalCode.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@country", cbbCountry.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@phone", txtPhone.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@fax", txtFax.Text);
                cmd.Parameters.Add(param);

                LoadSupplier();
                gvSupplier.Rows.Clear();
                cmd.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("Successfully");
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Update fail");
            }
            
        }
        private void LoadSupplier()
        {
            try
            {
                gvSupplier.Rows.Clear();
                SqlConnection con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "getsuppliers";
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    gvSupplier.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                        dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(),
                        dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
                }
                dr.Close();
                con.Close();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Load fail");
            }
        }


        
        private void DeleteSupplier()
        {
            try
            {
                SqlConnection con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "DeleteSuppliers";
                SqlParameter param = new SqlParameter("@supplierid", txtSupID.Text);
                cmd.Parameters.Add(param);
                gvSupplier.Rows.Clear();
                cmd.ExecuteNonQuery();
                LoadSupplier();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Delete fail");
            }
           
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            DialogResult rs = MessageBox.Show("Are you sure to delete ?",
                "Confirm", MessageBoxButtons.YesNo,
                MessageBoxIcon.Question);
            if (rs == DialogResult.Yes)
                DeleteSupplier();
                LoadSupplier();
                ClearAll();
        }
        

        

        private void txtContacttitle_TextChanged(object sender, EventArgs e)
        {

        }
        void FilterbyNational()
        {
            
        }

        private void btnFilter_Click(object sender, EventArgs e)
        {

        }

        private void cbbCountry_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            
                DialogResult rs = MessageBox.Show("Are you sure to update ?",
                    "Confirm", MessageBoxButtons.YesNo,
                    MessageBoxIcon.Question);
                if (rs == DialogResult.Yes)
                    UpdateSupplier();
                    LoadSupplier();
                    ClearAll();
        }

        private void txtSupID_TextChanged(object sender, EventArgs e)
        {

        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            AddSuppliers();
            LoadSupplier();
            
        }

        private void txtSupID_KeyPress(object sender, KeyPressEventArgs e)
        {
        }

        private void txtRegion_TextChanged(object sender, EventArgs e)
        {
           
        }
        bool validateInput()
        {
            string CompanyName = txtCompanyName.Text;
            string ContactName = txtContactName.Text;
            string Contacttitle = txtContacttitle.Text;
            string City = txtCity.Text;
            string Address = txtAddress.Text;
            string PostalCode = txtPostalCode.Text;
            bool error = false;

            if (CompanyName.Length == 0)
            {
                errorProvider1.SetError(txtCompanyName, "Please enter your company name!");
                error = true;
            }
            if (ContactName.Length == 0)
            {
                errorProvider1.SetError(txtContactName, "Please enter your contact name!");
                error = true;
            }
             if (Contacttitle.Length == 0)
            {
                errorProvider1.SetError(txtContacttitle, "Please enter your contact tittle!");
                error = true;
            }
             if (City.Length == 0)
             {
                 errorProvider1.SetError(txtCity, "Please enter your city name!");
                 error = true;
             }
             if (Address.Length == 0)
            {
                errorProvider1.SetError(txtAddress, "Please enter your address!");
                error = true;
            }
             
             if (PostalCode.Length == 0)
             {
                 errorProvider1.SetError(txtPostalCode, "Please enter your Postal Code!");
                 error = true;
             }
         
            if (txtPhone.MaskCompleted == false)
            {
                errorProvider1.SetError(txtPhone, "Please enter required digit");
                error = true;
            }
            if (error == true)
            {
                return false;
            }
            else
                errorProvider1.Clear();
            return true;
        }

        private void txtCompanyName_KeyDown(object sender, KeyEventArgs e)
        {

        }

        private void txtPhone_MaskInputRejected(object sender, MaskInputRejectedEventArgs e)
        {

        }

        private void txtSearch_TextChanged(object sender, EventArgs e)
        {

        }

        private void txtPostalCode_TextChanged(object sender, EventArgs e)
        {
            if (txtPostalCode.Text != "")
            {
                try
                {
                    int u = Int32.Parse(txtPostalCode.Text.Trim());
                }
                catch
                {
                    MessageBox.Show(" Please input number !");
                    txtPostalCode.Text = "";
                    txtPostalCode.Focus();
                    return;
                }
            }
        }

        private void txtFax_TextChanged(object sender, EventArgs e)
        {   
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            LoadSupplier();
            ClearAll();
        }
        private void searchCompanyName()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = @"server=HUNGVMSE61352;database=TSQLFundamentals2008;uid=sa;pwd=";
            con.Open();


            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "searchCompanyName";


            SqlParameter param = new SqlParameter("@companyname", SqlDbType.NVarChar, 40);
            param.Value = txtSearch.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                gvSupplier.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                    dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(),
                    dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
            }
            dr.Close();
            con.Close();
        }

        private void searchContactName()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = @"server=HUNGVMSE61352;database=TSQLFundamentals2008;uid=sa;pwd=";
            con.Open();


            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "searchContactName";


            SqlParameter param = new SqlParameter("@contactname", SqlDbType.NVarChar, 30);
            param.Value = txtSearch.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                gvSupplier.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                    dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(),
                    dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
            }
            dr.Close();
            con.Close();
        }

        private void searchContactTittle()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = @"server=HUNGVMSE61352;database=TSQLFundamentals2008;uid=sa;pwd=";
            con.Open();


            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "searchContactTittle";


            SqlParameter param = new SqlParameter("@contacttittle", SqlDbType.NVarChar, 15);
            param.Value = txtSearch.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                gvSupplier.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                    dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(),
                    dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
            }
            dr.Close();
            con.Close();
        }

        private void searchAddress()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = @"server=HUNGVMSE61352;database=TSQLFundamentals2008;uid=sa;pwd=";
            con.Open();


            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "searchAddress";


            SqlParameter param = new SqlParameter("@address", SqlDbType.NVarChar, 30);
            param.Value = txtSearch.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                gvSupplier.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                    dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(),
                    dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
            }
            dr.Close();
            con.Close();
        }

        private void searchCity()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = @"server=HUNGVMSE61352;database=TSQLFundamentals2008;uid=sa;pwd=";
            con.Open();


            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "searchCity";


            SqlParameter param = new SqlParameter("@city", SqlDbType.NVarChar, 15);
            param.Value = txtSearch.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                gvSupplier.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                    dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(),
                    dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
            }
            dr.Close();
            con.Close();
        }

        private void searchRegion()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = @"server=HUNGVMSE61352;database=TSQLFundamentals2008;uid=sa;pwd=";
            con.Open();


            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "searchRegion";


            SqlParameter param = new SqlParameter("@region", SqlDbType.NVarChar, 15);
            param.Value = txtSearch.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                gvSupplier.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                    dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(),
                    dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
            }
            dr.Close();
            con.Close();
        }

        private void searchPostalCode()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = @"server=HUNGVMSE61352;database=TSQLFundamentals2008;uid=sa;pwd=";
            con.Open();


            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "searchPostalCode";


            SqlParameter param = new SqlParameter("@postalcode", SqlDbType.NVarChar, 10);
            param.Value = txtSearch.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                gvSupplier.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                    dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(),
                    dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
            }
            dr.Close();
            con.Close();
        }

        private void searchCountry()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = @"server=HUNGVMSE61352;database=TSQLFundamentals2008;uid=sa;pwd=";
            con.Open();


            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "searchCountry";


            SqlParameter param = new SqlParameter("@country", SqlDbType.NVarChar, 15);
            param.Value = txtSearch.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                gvSupplier.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                    dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(),
                    dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
            }
            dr.Close();
            con.Close();
        }

        private void searchPhone()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = @"server=HUNGVMSE61352;database=TSQLFundamentals2008;uid=sa;pwd=";
            con.Open();


            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "searchPhone";


            SqlParameter param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
            param.Value = txtSearch.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                gvSupplier.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                    dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(),
                    dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
            }
            dr.Close();
            con.Close();
        }

        private void searchFax()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = @"server=HUNGVMSE61352;database=TSQLFundamentals2008;uid=sa;pwd=";
            con.Open();


            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "searchFax";


            SqlParameter param = new SqlParameter("@fax", SqlDbType.NVarChar, 24);
            param.Value = txtSearch.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                gvSupplier.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                    dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(),
                    dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
            }
            dr.Close();
            con.Close();
        }

        private void btSearch_Click(object sender, EventArgs e)
        {
            try
            {
                gvSupplier.Rows.Clear();
                string search = cbbFilter.Text;
                if (search == "Company Name")
                {
                    searchCompanyName();
                }
                else if (search == "Contact Name")
                {
                    searchContactName();
                }
                else if (search == "Contact Tittle")
                {
                    searchContactTittle();
                }
                else if (search == "Address")
                {
                    searchAddress();
                }
                else if (search == "City")
                {
                    searchCity();
                }
                else if (search == "Region")
                {
                    searchRegion();
                }
                else if (search == "PostalCode")
                {
                    searchPostalCode();
                }
                else if (search == "Country")
                {
                    searchCountry();
                }
                else if (search == "Phone")
                {
                    searchPhone();
                }
                else if (search == "Fax")
                {
                    searchFax();
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Search fail");
            }
            
        }

        

        
        
    }
}
